﻿# SQL Manager Lite for MySQL 5.3.1.7
# ---------------------------------------
# Host     : localhost
# Port     : 3306
# Database : flx_qcubed


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

SET FOREIGN_KEY_CHECKS=0;

DROP DATABASE IF EXISTS flx_qcubed;

CREATE DATABASE flx_qcubed
    CHARACTER SET 'utf8'
    COLLATE 'utf8_general_ci';

USE flx_qcubed;

#
# Dropping database objects
#

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS setting;
DROP TABLE IF EXISTS service;
DROP TABLE IF EXISTS project_service;
DROP TABLE IF EXISTS project_gallery;
DROP TABLE IF EXISTS project;
DROP TABLE IF EXISTS content_page_type;
DROP TABLE IF EXISTS content_page;
DROP TABLE IF EXISTS content_key_type;
DROP TABLE IF EXISTS content_key;
DROP TABLE IF EXISTS contact_phone;
DROP TABLE IF EXISTS contact_email;
DROP TABLE IF EXISTS contact;

#
# Structure for the contact table : 
#

CREATE TABLE contact (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  street VARCHAR(128) COLLATE utf8_general_ci NOT NULL,
  state VARCHAR(80) COLLATE utf8_general_ci NOT NULL,
  city VARCHAR(80) COLLATE utf8_general_ci NOT NULL,
  zipcode VARCHAR(20) COLLATE utf8_general_ci NOT NULL,
  notes VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL,
  google_maps_url VARCHAR(2048) COLLATE utf8_general_ci DEFAULT NULL,
  flickr_url VARCHAR(2048) COLLATE utf8_general_ci DEFAULT NULL,
  facebook_url VARCHAR(2048) COLLATE utf8_general_ci DEFAULT NULL,
  twitter_url VARCHAR(2048) COLLATE utf8_general_ci DEFAULT NULL,
  skype_url VARCHAR(2048) COLLATE utf8_general_ci DEFAULT NULL,
  msn_url VARCHAR(2048) COLLATE utf8_general_ci DEFAULT NULL,
  google_plus_url VARCHAR(2048) COLLATE utf8_general_ci DEFAULT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id)
)ENGINE=MyISAM
AUTO_INCREMENT=2 AVG_ROW_LENGTH=452 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the contact_email table : 
#

CREATE TABLE contact_email (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  contact_id INTEGER(11) NOT NULL,
  email VARCHAR(128) COLLATE utf8_general_ci NOT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX contact_email_email_uk USING BTREE (email),
   INDEX contact_email_contact_idx USING BTREE (contact_id)
)ENGINE=MyISAM
AUTO_INCREMENT=10 AVG_ROW_LENGTH=54 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the contact_phone table : 
#

CREATE TABLE contact_phone (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  contact_id INTEGER(11) NOT NULL,
  number VARCHAR(20) COLLATE utf8_general_ci NOT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX contact_phone_number_uk USING BTREE (number),
   INDEX contact_phone_contact_idx USING BTREE (contact_id)
)ENGINE=MyISAM
AUTO_INCREMENT=9 AVG_ROW_LENGTH=44 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the content_key table : 
#

CREATE TABLE content_key (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  content_page_type_id INTEGER(11) DEFAULT NULL,
  name VARCHAR(50) COLLATE utf8_general_ci NOT NULL,
  value TEXT COLLATE utf8_general_ci NOT NULL,
  content_key_type_id INTEGER(11) NOT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX content_key_type_content_name_uk USING BTREE (content_page_type_id, name),
   INDEX content_page_type_idx USING BTREE (content_page_type_id),
   INDEX content_key_type_idx USING BTREE (content_key_type_id)
)ENGINE=MyISAM
AUTO_INCREMENT=604 AVG_ROW_LENGTH=128 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the content_key_type table : 
#

CREATE TABLE content_key_type (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX content_key_type_name_uk USING BTREE (name)
)ENGINE=MyISAM
AUTO_INCREMENT=10 AVG_ROW_LENGTH=20 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the content_page table : 
#

CREATE TABLE content_page (
  content_page_type_id INTEGER(11) NOT NULL,
  url VARCHAR(255) COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY USING BTREE (content_page_type_id),
  UNIQUE INDEX content_page_url_uk USING BTREE (url)
)ENGINE=MyISAM
AVG_ROW_LENGTH=22 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the content_page_type table : 
#

CREATE TABLE content_page_type (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX content_page_type_name_uk USING BTREE (name)
)ENGINE=MyISAM
AUTO_INCREMENT=9 AVG_ROW_LENGTH=21 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the project table : 
#

CREATE TABLE project (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) COLLATE utf8_general_ci NOT NULL,
  date DATETIME NOT NULL,
  is_enable BIT(1) NOT NULL,
  description VARCHAR(255) COLLATE utf8_general_ci NOT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX project_name_uk USING BTREE (name)
)ENGINE=MyISAM
AUTO_INCREMENT=23 AVG_ROW_LENGTH=165 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the project_gallery table : 
#

CREATE TABLE project_gallery (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  project_id INTEGER(11) NOT NULL,
  description TEXT COLLATE utf8_general_ci NOT NULL,
  internal VARCHAR(256) COLLATE utf8_general_ci NOT NULL,
  url VARCHAR(1024) COLLATE utf8_general_ci NOT NULL,
  secure_url VARCHAR(1024) COLLATE utf8_general_ci DEFAULT NULL,
  thumb_url VARCHAR(1024) COLLATE utf8_general_ci NOT NULL,
  width INTEGER(11) NOT NULL,
  height INTEGER(11) NOT NULL,
  format VARCHAR(6) COLLATE utf8_general_ci NOT NULL,
  size INTEGER(11) NOT NULL,
  log_user VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id),
   INDEX project_gallery_project_idx USING BTREE (project_id)
)ENGINE=MyISAM
AUTO_INCREMENT=36 AVG_ROW_LENGTH=362 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the project_service table : 
#

CREATE TABLE project_service (
  project_id INTEGER(11) NOT NULL,
  service_id INTEGER(11) NOT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (project_id, service_id),
   INDEX project_service_project_idx USING BTREE (project_id),
   INDEX project_service_service_idx USING BTREE (service_id)
)ENGINE=MyISAM
AVG_ROW_LENGTH=26 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the service table : 
#

CREATE TABLE service (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) COLLATE utf8_general_ci NOT NULL,
  description TEXT COLLATE utf8_general_ci NOT NULL,
  is_enable BIT(1) NOT NULL DEFAULT 1,
  order INTEGER(11) NOT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX service_name_uk USING BTREE (name)
)ENGINE=MyISAM
AUTO_INCREMENT=10 AVG_ROW_LENGTH=248 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the setting table : 
#

CREATE TABLE setting (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  key VARCHAR(20) COLLATE utf8_general_ci NOT NULL,
  value VARCHAR(255) COLLATE utf8_general_ci NOT NULL,
  description VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX setting_key_uk USING BTREE (key)
)ENGINE=MyISAM
AUTO_INCREMENT=17 AVG_ROW_LENGTH=77 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

#
# Structure for the user table : 
#

CREATE TABLE user (
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(80) COLLATE utf8_general_ci NOT NULL,
  last_name VARCHAR(80) COLLATE utf8_general_ci NOT NULL,
  phone VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  mobile VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  email VARCHAR(128) COLLATE utf8_general_ci DEFAULT NULL,
  is_enable BIT(1) NOT NULL DEFAULT 1,
  is_admin BIT(1) NOT NULL DEFAULT 1,
  username VARCHAR(20) COLLATE utf8_general_ci NOT NULL,
  password VARCHAR(255) COLLATE utf8_general_ci NOT NULL,
  log_user VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  log_insert DATETIME DEFAULT NULL,
  log_update TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY USING BTREE (id),
  UNIQUE INDEX user_username_uk USING BTREE (username)
)ENGINE=MyISAM
AUTO_INCREMENT=7 AVG_ROW_LENGTH=80 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';



/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;